-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
ALTER FUNCTION [fn_GetTrangThaiLamViecInYear]
(
	-- Add the parameters for the function here
	@NhanVienID int,
	@Year varchar(4)
)
RETURNS int
AS
BEGIN
	declare @toDate as DateTime
	set @toDate = '12/31/' + @Year
	
	declare @var as int
	
	SELECT @var = ttlv.LoaiTrangThaiID 
	FROM NSTrangThaiLamViec ttlv
	WHERE
	ttlv.NhanVienID = @NhanVienID
	AND (
	(ttlv.NgayBatDau <= @toDate AND ttlv.NgayKetThuc >= @toDate AND ttlv.NgayKetThuc IS NOT NULL)
	OR (ttlv.NgayBatDau <= @toDate AND ttlv.NgayKetThuc IS NULL) )
	RETURN @var
END
GO

--SELECT dbo.fn_GetTrangThaiLamViecInYear(30,'2012') As TRim

